/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.pac4.only5.manager.impl;

import edu.uoc.tdp.pac4.only5.domain.Booking;
import edu.uoc.tdp.pac4.only5.domain.EnumStateBooking;
import edu.uoc.tdp.pac4.only5.domain.Resource;
import edu.uoc.tdp.pac4.only5.exceptions.EespaisDatabaseException;
import edu.uoc.tdp.pac4.only5.manager.DatabaseConnectionManager;
import edu.uoc.tdp.pac4.only5.manager.ResourceManager;
import edu.uoc.tdp.pac4.only5.vo.ResourceBookingVO;
import edu.uoc.tdp.pac4.only5.tools.DateTool;
import java.rmi.RemoteException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Gerard
 * @author Alex
 */
public class ResourceManagerImpl extends AbstractManager implements ResourceManager {

    public ResourceManagerImpl(DatabaseConnectionManager databaseConnectionManager) throws RemoteException {
        super(databaseConnectionManager);
        
    }

    @Override
    public Resource getResource(String resourceId) throws RemoteException {
        Resource resource = null;
        try {
            Integer varInt = Integer.parseInt(resourceId);
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM all_resources WHERE resourceid = ?");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setInt(1, varInt);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while(resultSet.next()){
                resource = getResource(resultSet);
            }
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }
       return resource;
    }
    
    /**
     * @autor Alex
     * @param resource
     * @return void
     */
    @Override
    public void addResource(Resource resource) throws RemoteException {
        try {
            String insertStatement = "INSERT INTO all_resources "
                    + "(resourceid,resourcedescription,amount,price_hour,price_day,"
                    + "creation_date,creation_user,inactivation_date,inactivation_user,record_date) "
                    + "VALUES (" + resource.getResource_id()
                    + ",'" + resource.getResource_desc() + "'," + resource.getAmount() 
                    + "," + resource.getPrice_hour() + "," + resource.getPrice_day() 
                    + ",'" + resource.getCreationDate() + "','" + resource.getCreationUser() 
                    + "','" + resource.getInactivationDate() + "','" + resource.getInactivationUser()
                    + "','" + resource.getRecordDate()+"');";
            
            StringBuilder sql = new StringBuilder();
            sql.append(insertStatement);
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(NullPointerException e) {
            Logger.getLogger(SpaceManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }

    }

    @Override   
    public void modifyResource(Resource resource) throws RemoteException {
        try {
            System.out.println("Dins modifyResource");
            String insertStatement = "UPDATE all_resources set "
                    + "resourcedescription='" + resource.getResource_desc() + "',amount=" + resource.getAmount() 
                    + ",price_hour=" + resource.getPrice_hour() + ",price_day=" + resource.getPrice_day() 
                    + ",creation_date='" + resource.getCreationDate() + "',creation_user='" + resource.getCreationUser() 
                    + "',inactivation_date='" + resource.getInactivationDate() 
                    + "',inactivation_user='" + resource.getInactivationUser() 
                    + "',record_date='" + resource.getRecordDate() 
                    + "' WHERE resourceid= ?";
            
            Integer varInt = resource.getResource_id();
            StringBuilder sql = new StringBuilder();
            sql.append(insertStatement);
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setInt(1, varInt);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }  catch(NullPointerException e) {
            Logger.getLogger(SpaceManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }
    }

    @Override
    public void deleteResource(String resourceId) throws RemoteException {
        try {
            Integer varInt = Integer.parseInt(resourceId);
            StringBuilder sql = new StringBuilder();
            sql.append("DELETE FROM all_resources WHERE resourceId = ?");
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setInt(1, varInt);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();

        } catch (EespaisDatabaseException ex) {
            //TODO handle errors!!!
            ex.printStackTrace();
        } catch(SQLException ex){
            //TODO handle errors!!!
            ex.printStackTrace();
        }  catch(NullPointerException e) {
            Logger.getLogger(UserManagerImpl.class.getName()).log(Level.SEVERE, null, e);
        }
        
       return;    }

    
    @Override
    public List<Resource> getNotBookedResources() throws RemoteException, EespaisDatabaseException {
        List<Resource> resources = new ArrayList<Resource>();
        try {
            StringBuilder sql = new StringBuilder();
            sql .append("select all_resources.*, amount - count(booking_resources.resourceid) as notBooked") 
                .append(" from booking_resources right join all_resources on booking_resources.resourceid = all_resources.resourceid inner join bookings on bookings.bookingid = booking_resources.bookingid")
                .append(" where bookings.status = ? and bookings.start_time < ? and bookings.end_time < ?")
                .append(" group by booking_resources.resourceid, all_resources.resourceid, all_resources.resourcedescription, all_resources.amount, all_resources.price_hour, all_resources.price_day, all_resources.creation_date, all_resources.creation_user, all_resources.inactivation_date, all_resources.inactivation_user, all_resources.record_date");
            
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            
            preparedStatement.setString(1, EnumStateBooking.CURSADA.name());
            preparedStatement.setDate(2, DateTool.getInstance().getSqlDate(new Date()));
            preparedStatement.setDate(3, DateTool.getInstance().getSqlDate(new Date()));
            
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while (resultSet.next()) {
                Resource r = getResource(resultSet);
                r.setNotBooked(resultSet.getInt("notBooked"));
                resources.add(r);
            }
        } catch (SQLException ex) {
          throw new EespaisDatabaseException(ex.getMessage(), ex);
        } 
        return resources;
    }
    
    @Override
    public void saveBookedResources(Booking booking) throws RemoteException, EespaisDatabaseException {
        try {
            getDatabaseConnectionManager().openConnection();
            getDatabaseConnectionManager().setAutoCommit(false);
            List<PreparedStatement> listPS = new ArrayList<PreparedStatement>();
            for(Resource resource : booking.getExtraResources()){
             PreparedStatement ps = getDatabaseConnectionManager().createPreparedStatement("INSERT INTO booking_resources (bookingid,resourceid,record_date) VALUES (?,?,?)");
             ps.setInt(1, booking.getId());
             ps.setInt(2, resource.getResource_id());
             ps.setDate(3, DateTool.getInstance().getSqlDate(new Date()));
             listPS.add(ps);
            }
            
            for(PreparedStatement ps : listPS){
                ps.execute();
            }
            
            getDatabaseConnectionManager().commit();
        } catch (SQLException ex) {
            throw new EespaisDatabaseException(ex.getMessage(), ex);
        }
    }


    public static Resource getResource(ResultSet rs) throws RemoteException, SQLException {
        Resource resource = new Resource();
        resource.setResource_id(rs.getInt("resourceid"));
        resource.setResource_desc(rs.getString("resourcedescription"));
        resource.setAmount(rs.getInt("amount"));
        resource.setPrice_hour(rs.getDouble("price_hour"));
        resource.setPrice_day(rs.getDouble("price_day"));
        resource.setCreationDate(rs.getDate("creation_date"));
        resource.setCreationUser(rs.getString("creation_user"));
        resource.setInactivationDate(rs.getDate("inactivation_date"));
        resource.setInactivationUser(rs.getString("inactivation_user"));
        resource.setRecordDate(rs.getDate("record_date"));      
        return resource;
    }

      
        
     public List<ResourceBookingVO> getResourceBooking(int bookingId) throws RemoteException, EespaisDatabaseException {
        List<ResourceBookingVO> resources = new ArrayList<ResourceBookingVO>();
       
        try {
            StringBuilder sql = new StringBuilder();

            sql.append("select booking_resources.bookingid,booking_resources.resourceid,count(*) as numero,")
               .append(" all_resources.resourcedescription ,all_resources.price_hour,all_resources.price_day,")
               .append(" all_resources.amount")
               .append(" from booking_resources ")
               .append(" left join all_resources on booking_resources.resourceid = all_resources.resourceid ")
               .append(" where bookingid =?")
               .append(" group by  booking_resources.bookingid,booking_resources.resourceid,all_resources.resourcedescription,")
               .append(" all_resources.price_hour,all_resources.price_day,all_resources.amount");
          
            getDatabaseConnectionManager().openConnection();
            PreparedStatement preparedStatement = getDatabaseConnectionManager().createPreparedStatement(sql.toString());
            preparedStatement.setInt(1, bookingId);
            preparedStatement.execute();
            getDatabaseConnectionManager().closeConnection();
            ResultSet resultSet = preparedStatement.getResultSet();
            while (resultSet.next()) {
                Resource r = new Resource();
                r.setResource_id(resultSet.getInt("resourceid"));
                
                r.setResource_desc(resultSet.getString("resourcedescription"));
                r.setPrice_day(resultSet.getDouble("price_day"));
                r.setPrice_hour(resultSet.getDouble("price_hour"));
                r.setAmount(resultSet.getInt("amount"));
                r.setNotBooked(resultSet.getInt("numero"));
                resources.add(new ResourceBookingVO(r, resultSet.getInt("numero")));
               
            }
        } catch (SQLException ex) {
           throw new EespaisDatabaseException(ex.getMessage(), ex);
        }

        return resources;
    }

     
     
    
    

    
}
